﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using ProjetoBe.cs;
using System.Data;
using ProjetoDalc.cs;

namespace ProjetoDalc.cs
{
    public class ComboDalc
    {

        #region Trazer todos os combos

        public DataTable buscarCombos()
        {
            SqlDataReader reader;
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adpter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "SELECT * FROM COMBO";

                conexao.Open();
                reader = command.ExecuteReader();

                DataTable tabelaCombos = new DataTable();
                tabelaCombos.Load(reader);

                return tabelaCombos;
            }
        }

        #endregion

        #region Inserir novos combo

        public void InserirCombos(ComboBe combo)
        {
            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();
                command.Connection = conexao;
                command.CommandText = "INSERT INTO COMBO VALUES(@COMBO_NOME, @COMBO_DESCRICAO, @COMBO_VEZES)";
                command.Parameters.AddWithValue("COMBO_NOME", combo.nome);
                command.Parameters.AddWithValue("COMBO_DESCRICAO", combo.descricao);
                command.Parameters.AddWithValue("COMBO_VEZES", combo.vezes = 0);

                adapter.InsertCommand = command;

                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }

        }

        #endregion

        #region Alterar Combos Existentes

        public void alterarCombo(ComboBe objeto)
        {

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;

                command.CommandText = "UPDATE COMBO SET COMBO_NOME = @nome, COMBO_DESCRICAO = @DESCRICAO WHERE COMBO_ID = @id";

                command.Parameters.AddWithValue("@id", objeto.id);
                command.Parameters.AddWithValue("@nome", objeto.nome);
                command.Parameters.AddWithValue("@descricao", objeto.descricao);


                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }

        #endregion

        #region Trazer os valores do combo

        public ComboBe preencherCamposCombo(int id)
        {
            SqlDataReader reader;
            ComboBe combo = new ComboBe();


            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;
                command.CommandText = "SELECT * FROM COMBO WHERE COMBO_ID = @id;";
                command.Parameters.AddWithValue("@id", id);
                conexao.Open();
                reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        combo.nome = reader["COMBO_NOME"].ToString();
                        combo.descricao = reader["COMBO_DESCRICAO"].ToString();
                        combo.vezes = Convert.ToInt16(reader["COMBO_VEZES"]);

                    }
                }
                conexao.Close();
            }
            return combo;
        }

        #endregion

        #region Excluir combos

        public void excluirCombo(ComboBe objeto)
        {

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;

                command.CommandText = "DELETE FROM COMBO WHERE COMBO_ID = @id";

                command.Parameters.AddWithValue("@id", objeto.id);

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }

        #endregion

        #region Aumentar numero de vezes de utilização do combo

        public void aumentarVezes(ComboBe objeto)
        {

            using (SqlConnection conexao = Connection.CriarConexao())
            {
                SqlCommand command = new SqlCommand();
                SqlDataAdapter adapter = new SqlDataAdapter();

                command.Connection = conexao;

                command.CommandText = "UPDATE COMBO SET COMBO_VEZES = @vezes WHERE COMBO_ID = @id";

                command.Parameters.AddWithValue("@id", objeto.id);
                command.Parameters.AddWithValue("@vezes", objeto.vezes);

                adapter.InsertCommand = command;
                conexao.Open();
                command.ExecuteNonQuery();
                conexao.Close();
            }
        }

        #endregion

    }
}
